package com.qdu.service.impl;

import java.util.List;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.qdu.entity.Student;
import com.qdu.entity.Users;
import com.qdu.mapper.StudentMapper;
import com.qdu.mapper.UsersMapper;
import com.qdu.service.StudentService;
import com.qdu.util.DateUtil;

@Service
public class StudentServiceImpl implements StudentService{

	@Autowired
	StudentMapper studentMapper;

	@Autowired
	UsersMapper usersMapper;
	
	@Autowired
	private PasswordEncoder pwdEncoder;
	
	@Override
	public Student selectStudentById(String id) {
		return studentMapper.selectStudentById(id);
	}

	@Override
	public boolean addStudent(Student Student, int bid) {
		return studentMapper.addStudent(Student, bid);
	}

	@Override
	public boolean modifyStudent(Student Student, int bid) {
		return studentMapper.modifyStudent(Student, bid);
	}

	@Override
	public boolean deleteStudentById(String id) {
		return studentMapper.deleteStudentById(id);
	}

	@Override
	public List<Student> selectAllStudent() {
		return studentMapper.selectAllStudent();
	}

	@Override
	public List<Student> selectAllStudentByBid(int bid) {
		return studentMapper.selectAllStudentByBid(bid);
	}
	
	@Override
	public XSSFWorkbook showStudentByBid(int bid) {
		// 查询要下载的数据
		List<Student> stuList = studentMapper.selectAllStudentByBid(bid);
		// XSSFWorkbook–Excel中的xlsx版本
		XSSFWorkbook wb = new XSSFWorkbook();
		// 创建一张表
		Sheet sheet = wb.createSheet();
		// 创建第一行，索引从零开始，第一行一般作为表头
		Row titleRow = sheet.createRow(0);
		// 创建列，索引从零开始
		titleRow.createCell(0).setCellValue("序号");
		titleRow.createCell(1).setCellValue("学号");
		titleRow.createCell(2).setCellValue("姓名");
		titleRow.createCell(3).setCellValue("班级");
		titleRow.createCell(4).setCellValue("性别");
		int rowNum = 1;
		for (Student student : stuList) {
			// rowNum=1 即 创建第二行，一般是数据的第一行
			Row row = sheet.createRow(rowNum);
			row.createCell(0).setCellValue(rowNum);
			row.createCell(1).setCellValue(student.getSrollno());
			row.createCell(2).setCellValue(student.getSname());
			row.createCell(3).setCellValue(student.getBatch().getBname());
			row.createCell(4).setCellValue(student.getSgender());
			rowNum++;
		}
		return wb;
	}

	@Override
	public boolean uploadStudent(MultipartFile file) throws Exception {

		Workbook workbook = WorkbookFactory.create(file.getInputStream());

		Sheet sheet = workbook.getSheet("sheet1");

		int num = sheet.getLastRowNum();

		for (int i = 1; i <= num; i++) {
			Row row = sheet.getRow(i);
			Cell cell1 = row.getCell(0);
			cell1.setCellType(CellType.STRING);
			Cell cell2 = row.getCell(1);
			Cell cell3 = row.getCell(2);
			Cell cell4 = row.getCell(3);
			Cell cell5 = row.getCell(4);
			Cell cell6 = row.getCell(5);
			if(cell6 == null) {
				cell6 = row.createCell(5);
				cell6.setCellType(CellType.STRING);
			}
			Cell cell7 = row.getCell(6);
			if(cell7 == null) {
				cell7 = row.createCell(6);
				cell7.setCellType(CellType.STRING);
			}
			Cell cell8 = row.getCell(7);
			Cell cell9 = row.getCell(8);
			Cell cell10 = row.getCell(9);
			Cell cell11 = row.getCell(10);
			Cell cell12 = row.getCell(11);
			cell12.setCellType(CellType.STRING);
			
			Student student = studentMapper.selectStudentById(cell1.getStringCellValue());
			if (student == null) {

				Student stu = new Student();
				Users user = new Users();
				stu.setSrollno(cell1.getStringCellValue());
				stu.setSname(cell2.getStringCellValue());
				stu.setSgender(cell4.getStringCellValue());
				stu.setBirthday(DateUtil.formatDate(cell5.getDateCellValue()));
				if (cell6.getStringCellValue().equals("")) {
					
				} else {
					stu.setSque(cell6.getStringCellValue());
				}
				if (cell7.getStringCellValue().equals("")) {

				} else {
					stu.setSans(cell7.getStringCellValue());
				}
				stu.setEnrolment(DateUtil.formatDate(cell8.getDateCellValue()));
				stu.setLocation(cell9.getStringCellValue());
				stu.setPolitical(cell10.getStringCellValue());
				stu.setImg(cell11.getStringCellValue());
				user.setUid(cell1.getStringCellValue());
				user.setUpassword(pwdEncoder.encode(cell12.getStringCellValue()));
				usersMapper.addUser(user, 1);
				studentMapper.addStudent(stu, (int) cell3.getNumericCellValue());
			} else {

			}
		}

		return true;
	}

	@Override
	public List<Student> selectStudentByKeyWord(String keyword) {
		return studentMapper.selectStudentByKeyWord(keyword);
	}
}
